var config = require('./db-config.js');
var mysql = require('mysql');

config.connectionLimit = 10;
var connection = mysql.createPool(config);

/* -----------------------------------------------
/* ------------------- Route Handlers --------------- */
/* -------------------------------------------------- */


/* ---- Q1a (Dashboard) ---- */
function getAllGenres(req, res) {


  var query = `
    SELECT DISTINCT g.genre 
    FROM Genres g;
  `;
  connection.query(query, function(err, rows, fields) {
    if (err) console.log(err);
    else {
      res.json(rows);
    }
  })

};
  

/* ---- Q1b (Dashboard) ---- */
function getTopInGenre(req, res) {

  var inputGenre = req.params.genre

  var query = `
    SELECT m.title, m.rating, m.vote_count
    FROM Genres g, Movies m
    WHERE g.genre = '${inputGenre}' AND g.movie_id=m.id
    ORDER BY m.rating DESC, m.vote_count
    DESC
    LIMIT 10;  
  `;


   connection.query(query, function(err, rows, fields) {
    if (err) console.log(err);
    else {
      res.json(rows);
    }
  })
  
};

/* ---- Q2 (Recommendations) ---- */
function getRecs(req, res) {

  var inputGenre = req.params.recommendations

  var query = `
  select g.genre
  from Genres g inner join Movies m on g.movie_id = m.id
  where m.title = '${inputGenre}';
  `;




  connection.query(query, function(err, rows, fields) {
    if (err) console.log(err);
    else {
      if (rows.length === 0) {
        res.json([])
      }
      let genres = rows.map(item => `${item.genre}`)
      let q2 = `
      SELECT m.*, GROUP_CONCAT(g.genre) genre  FROM movies m INNER JOIN genres g ON m.id=g.movie_id
      GROUP BY id
      ORDER BY avg(m.rating) DESC,  avg(m.vote_count) DESC;
      `
      connection.query(q2, (err, q2Ret) => {
        if (err) {
          console.log(err)
          return;
        }
        let ret = []
        //console.log(q2Ret)
        for (let eachResult of q2Ret) {

          let g = eachResult.genre.split(',')
         
          if (genres.filter(gen => g.indexOf(gen) === -1).length === 0 && eachResult.title !== inputGenre) {
            
            ret.push(eachResult)
          }
          if (ret.length === 5) {
            break
          }
        }
        res.json(ret)
      })
    }
  })
  

  
};

/* ---- (Best Genres) ---- */
function getDecades(req, res) {
	var query = `
    SELECT DISTINCT (FLOOR(year/10)*10) AS decade
    FROM (
      SELECT DISTINCT release_year as year
      FROM Movies
      ORDER BY release_year
    ) y
  `;
  connection.query(query, function(err, rows, fields) {
    if (err) console.log(err);
    else {
      res.json(rows);
    }
  });
}


/* ---- Q3 (Best Genres) ---- */
function bestGenresPerDecade(req, res) {

  var inputDecades = req.params.bestGenresPerDecade
  console.log(inputDecades)
  var query = `
  select distinct (genre) from Genres;
  `;
  const q2 = `
  select genre, IFNULL(AVG(m.rating), 0) as average from Genres g left outer join  Movies m on g.movie_id = m.id
  where release_year >= ${Number(inputDecades)} and release_year < ${Number(inputDecades) + 10} group by genre;
  `
  console.log(q2)

  let ret = [];
   connection.query(query, function(err, rows) {
    if (err) console.log(err);
    else {
      connection.query(q2, (err, ret2) => {
        rows.forEach(item => {
          let output = ret2.find(each => each.genre === item.genre)
         
          ret.push({
            genre: item.genre,
            avgRating: output && output.average || 0
          })
        })
        ret.sort((a, b) =>{
          if (a.avgRating !== b.avgRating) {
            return b.avgRating - a.avgRating
          } else {
            return a.genre.localeCompare(b.genre)
          }
        })
        res.json(ret);
      })
     
    }
  })
};

// The exported functions, which can be accessed in index.js.
module.exports = {
	getAllGenres: getAllGenres,
	getTopInGenre: getTopInGenre,
	getRecs: getRecs,
	getDecades: getDecades,
  bestGenresPerDecade: bestGenresPerDecade
}